/*
 * 作者：姚俊
 * 功能：项目的逻辑处理
 * 日期：2015-05-29
 */
package admin.service.fruits;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import beanDao.MapToBean;

import teacher.bean.Project;
import teacher.bean.Teacher;
import tools.StringDate;
import database.dao.MysqlCrud;

public class ProjectService {
	
	
	/**
	 * @category 根据工程项目的ID来查询一个工程项目
	 * @author yaojun
	 * @param Project Project 传入一个工程项目信息的对象，该对象必须有工程项目信息的ID
	 * @return Project 返回的是一个工程项目信息的对象
	 */
	public Project getProject(Project project)
	{
		Project pro =new Project();
		String sql="select * from Project where proId=?";
		String[]  parameter={String.valueOf(project.getProId())};
        MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> lists=mysqlcurd.SelectData(sql, parameter);
		if(lists.size()>0&&lists!=null)
		{	
			Map map=lists.get(0);
			pro=new MapToBean().MapToProject(map);
		}
		
		return pro;
	}	
	
	/**
	 * @category 根据工程项目信息的ID来修改这个工程项目的信息
	 * @author yaojun
	 * @param inn1 Project 传入一个要用于修改的Project对象
	 * @param inn2 Project 传入一个根据这个Project对象的ID来查找需要修改的哪一条工程项目信息数据
	 * @return boolean 成功则返回true
	 */
	public boolean updateProject(Project project1,Project project2)
	{
		boolean b=false;
		String sql="update Project set name=?,level=?,source=?," +
		"funds=?,arriveFunds=?,leader=?,others=?,beginDate=?,state=?," +
		"lowDepartment=?,class=?,number=?,finishDate=?,upload=?,teaId=?,kind=?,progress=? where proId=?";
		
		String[] parameter={
				String.valueOf(project1.getName()),
				String.valueOf(project1.getLevel()),
				String.valueOf(project1.getSource()),
				String.valueOf(project1.getFunds()),
				String.valueOf(project1.getArriveFunds()),
				String.valueOf(project1.getLeader()),
				String.valueOf(project1.getOthers()),
				String.valueOf(StringDate.DateToString(project1.getBeginDate())),
				String.valueOf(project1.getState()),
				String.valueOf(project1.getLowDepartment()),
				String.valueOf(project1.getClass_()),
				String.valueOf(project1.getNumber()),
				String.valueOf(StringDate.DateToString(project1.getFinishDate())),
				String.valueOf(project1.getUpload()),
				String.valueOf(project1.getTeaId()),
				String.valueOf(project1.getKind()),
				String.valueOf(project1.getProgress()),
				String.valueOf(project2.getProId())

		};
		
		MysqlCrud mysqlcurd=new MysqlCrud();
		b=mysqlcurd.InsertAndUpdateData(sql, parameter);
		return b;
	}
	
	
	/**
	 * @category 分页查询所有项目
	 * @return projects List<Project> 返回查询到的论文列表
	 */
	public List<Project> getSegregateProjectByAll(int pageNow,int pageSize) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as P WHERE P.state>3 and P.proId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Project> projects=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projects.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projects;
	}
	
	/**
	 * @category 根据名称分页查询所有项目
	 * @return projects List<Project> 返回查询到的论文列表
	 */
	public List<Project> getSegregateProjectByName(String name,int pageNow,int pageSize) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as P WHERE P.state>3 and P.name like '%"+name+"%' and P.proId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Project> projects=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projects.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projects;
	}
	
	
	/**
	 * @category 分与查询所有项目
	 * @return projects List<Project> 返回查询到的论文列表
	 */
	public List<Project> getSegregateProjectByDate(Date date1,Date date2,int pageNow,int pageSize) {
		String sql= "select * from (select * from project order by state,level,kind,class,finishDate desc) as P WHERE P.state>3 and P.finishDate>='"+date1+"' and P.finishDate<='"+date2+"' and P.proId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
		int i=0;
		List<Project> projects=new ArrayList<Project>();
		MysqlCrud mysqlcurd=new MysqlCrud();
		List<Map> list = mysqlcurd.SelectData(sql, null);
		//System.out.println(list.size());
		while(i<list.size()){
			Map map=list.get(i);
			projects.add(new MapToBean().MapToProject(map));
			i++;
		}
		return projects;
	}
	
	
	/**
	 * @category 根据名称统计项目的数量
	 * @return sum int 项目的数量
	 */
		public int getCountProjectByName(String name)
		{
			String sql=null;
			int count=0;
			if(name.equals("")||name==null)
			{
				sql="select count(*) from project where state>3";
			}
			else
			{
				sql="select count(*) from project where state>3 and name like '%"+name+"%'";
			}
			
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		/**
		 * @category 根据日期统计项目的数量
		 * @return count int 项目的数量
		 */
		public int getCountProjectByDate(Date date1,Date date2)
		{
			int count=0;
			String sql="select count(*) from project where state>3 and finishDate>='"+date1+"' and finishDate<='"+date2+"'";
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, null);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照审核状态来分页查询所有的工程项目
		 * @param state 4表示未审核，5表示已经审核
		 * @return List
		 */
		public List<Project> getSegregateProjectByState(int state,int pageNow,int pageSize) {
			String sql= "select * from project WHERE state=? and proId LIMIT " +
				""+(pageNow-1)+" ,"+pageSize+"";
			int i=0;
			List<Project> projects=new ArrayList<Project>();
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> list = mysqlcurd.SelectData(sql,parameter);
			//System.out.println(list.size());
			while(i<list.size()){
				Map map=list.get(i);
				projects.add(new MapToBean().MapToProject(map));
				i++;
			}
			return projects;
		}
		
		
		/**
		 * @category 根据审核状态统计项目的数量
		 * @return count int 项目的数量
		 */
		public int getCountProjectByState(int state)
		{
			int count=0;
			String sql="select count(*) from project where state=?";
			MysqlCrud mysqlcurd=new MysqlCrud();
			String[]  parameter={String.valueOf(state)};
			List<Map> lists=mysqlcurd.SelectData(sql,parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的项目并进行分页,针对特定的方向
		 * @return projects List<Project> 返回查询到的论文列表
		 */
		public List<Project> getSegregateProjectByDirection(Project project,int direction,int pageNow,int pageSize,int state,List<String> orders) {
			String sql=null;

			if(orders.size()>0)
			{
				
				String sqlOrder="";
		
				for(int i=0;i<orders.size();i++)
				{
				
					if(i!=orders.size()-1)
					sqlOrder+=orders.get(i)+",";
					else
					sqlOrder+=orders.get(i);
				
				}
				
				sql="select * from (select * from project order by "+sqlOrder+") as P,teacher as T where P.teaId=T.teaId and T.direction=? and p.state>'"+state+"' ";
				
			}
			else
			 sql="select * from project as P,teacher as T where P.teaId=T.teaId and T.direction=? and p.state>'"+state+"' ";
			int j=0;
			System.out.println(sql);
			String sql2=" and proId LIMIT "+(pageNow-1)+" ,"+pageSize+" ";
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			List<Project> projects=new ArrayList<Project>();
			/*System.out.println(a);
			System.out.println("laohe");
			
			System.out.println(b)*/;
		//	paras.add("%level%");
			/*paras.add("%kind%");
			paras.add("%class%");
			paras.add("%finishDate%");*/
			paras.add(String.valueOf(direction));
			if(project!=null)
			{
				if(project.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(project.getName())+"%");
				}
				if(project.getLevel()!=null)
				{
					sql1.add(" P.level=? ");
					paras.add(String.valueOf(project.getLevel()));
				}
				if(project.getFinishDate()!=null)
				{
					sql1.add(" P.finishDate>=? and P.finishDate<=? ");
					paras.add(String.valueOf(project.getBeginDate()));
					paras.add(String.valueOf(project.getFinishDate()));
				}
				if(project.getSource()!=null)
				{
					sql1.add(" P.source=? ");
					paras.add(String.valueOf(project.getSource()));
				}
				if(project.getFunds()!=null)
				{
					sql1.add(" P.funds=? ");
					paras.add(String.valueOf(project.getFunds()));
				}
				if(project.getArriveFunds()!=null)
				{
					sql1.add(" P.arriveFunds=? ");
					paras.add(String.valueOf(project.getArriveFunds()));
				}
				if(project.getLeader()!=null)
				{
					sql1.add(" P.leader like ? ");
					paras.add("%"+String.valueOf(project.getLeader())+"%");
				}
				if(project.getOthers()!=null)
				{
					sql1.add(" P.others like ? ");
					paras.add("%"+String.valueOf(project.getOthers())+"%");
				}
				if(project.getClass_()!=null)
				{
					sql1.add(" P.class=? ");
					paras.add(String.valueOf(project.getClass_()));
				}
				if(project.getNumber()!=null)
				{
					sql1.add(" P.number=? ");
					paras.add(String.valueOf(project.getNumber()));
				}
				if(project.getKind()!=null)
				{
					sql1.add(" P.kind=? ");
					paras.add(String.valueOf(project.getKind()));
				}
				if(project. getProgress()!=null)
				{
					sql1.add(" P.progress=? ");
					paras.add(String.valueOf(project. getProgress()));
				}
			}
			
			
				
				for(int i=0;i<sql1.size();i++)
				{
					sql+="and"+sql1.get(i);
				} 
			
		
			//加入最后一个,该字符是用于分页
			sql+=sql2;
			
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			System.out.println(sql);
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				projects.add(new MapToBean().MapToProject(map));
				j++;
			}
			return projects;
		}
		
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的项目,针对特定的方向
		 * @return projects List<Project> 返回查询到的论文列表
		 */
		public List<Project> getProjectByDirection(Project project,int direction,int state) {
//			String sql= "select * from project WHERE finishDate>='"+date1+"' and finishDate<='"+date2+"' and proId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select * from project as P,teacher as T where P.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			int j=0;
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			List<Project> projects=new ArrayList<Project>();
			
			paras.add(String.valueOf(direction));
			if(project!=null)
			{
				if(project.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(project.getName())+"%");
				}
				if(project.getLevel()!=null)
				{
					sql1.add(" P.level=? ");
					paras.add(String.valueOf(project.getLevel()));
				}
				if(project.getFinishDate()!=null)
				{
					sql1.add(" P.finishDate>=? and P.finishDate<=? ");
					paras.add(String.valueOf(project.getBeginDate()));
					paras.add(String.valueOf(project.getFinishDate()));
				}
				if(project.getSource()!=null)
				{
					sql1.add(" P.source=? ");
					paras.add(String.valueOf(project.getSource()));
				}
				if(project.getFunds()!=null)
				{
					sql1.add(" P.funds=? ");
					paras.add(String.valueOf(project.getFunds()));
				}
				if(project.getArriveFunds()!=null)
				{
					sql1.add(" P.arriveFunds=? ");
					paras.add(String.valueOf(project.getArriveFunds()));
				}
				if(project.getLeader()!=null)
				{
					sql1.add(" P.leader like ? ");
					paras.add("%"+String.valueOf(project.getLeader())+"%");
				}
				if(project.getOthers()!=null)
				{
					sql1.add(" P.others like ? ");
					paras.add("%"+String.valueOf(project.getOthers())+"%");
				}
				if(project.getClass_()!=null)
				{
					sql1.add(" P.class=? ");
					paras.add(String.valueOf(project.getClass_()));
				}
				if(project.getNumber()!=null)
				{
					sql1.add(" P.number=? ");
					paras.add(String.valueOf(project.getNumber()));
				}
				if(project.getKind()!=null)
				{
					sql1.add(" P.kind=? ");
					paras.add(String.valueOf(project.getKind()));
				}
				if(project. getProgress()!=null)
				{
					sql1.add(" P.progress=? ");
					paras.add(String.valueOf(project. getProgress()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			} 
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				projects.add(new MapToBean().MapToProject(map));
				j++;
			}
			return projects;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的项目
		 * @return projects List<Project> 返回查询到的论文列表
		 */
		public List<Project> getProjectByAll(Project project,int direction,int state) {

			String sql="select * from project as P,teacher as T where P.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			int j=0;
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			List<Project> projects=new ArrayList<Project>();
			paras.add(String.valueOf(direction));
			
			if(project!=null)
			{
				if(project.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(project.getName())+"%");
				}
				if(project.getLevel()!=null)
				{
					sql1.add(" P.level=? ");
					paras.add(String.valueOf(project.getLevel()));
				}
				if(project.getFinishDate()!=null)
				{
					sql1.add(" P.finishDate>=? and P.finishDate<=? ");
					paras.add(String.valueOf(project.getBeginDate()));
					paras.add(String.valueOf(project.getFinishDate()));
				}
				if(project.getSource()!=null)
				{
					sql1.add(" P.source=? ");
					paras.add(String.valueOf(project.getSource()));
				}
				if(project.getFunds()!=null)
				{
					sql1.add(" P.funds=? ");
					paras.add(String.valueOf(project.getFunds()));
				}
				if(project.getArriveFunds()!=null)
				{
					sql1.add(" P.arriveFunds=? ");
					paras.add(String.valueOf(project.getArriveFunds()));
				}
				if(project.getLeader()!=null)
				{
					sql1.add(" P.leader like ? ");
					paras.add("%"+String.valueOf(project.getLeader())+"%");
				}
				if(project.getOthers()!=null)
				{
					sql1.add(" P.others like ? ");
					paras.add("%"+String.valueOf(project.getOthers())+"%");
				}
				if(project.getClass_()!=null)
				{
					sql1.add(" P.class=? ");
					paras.add(String.valueOf(project.getClass_()));
				}
				if(project.getNumber()!=null)
				{
					sql1.add(" P.number=? ");
					paras.add(String.valueOf(project.getNumber()));
				}
				if(project.getKind()!=null)
				{
					sql1.add(" P.kind=? ");
					paras.add(String.valueOf(project.getKind()));
				}
				if(project. getProgress()!=null)
				{
					sql1.add(" P.progress=? ");
					paras.add(String.valueOf(project. getProgress()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			} 
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				projects.add(new MapToBean().MapToProject(map));
				j++;
			}
			return projects;
		}
		
		
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的项目的数量,针对特定的方向
		 * @return int 
		 */
		public int getProjectCountByDirection(Project project,int direction,int state) {
//			String sql= "select * from project WHERE finishDate>='"+date1+"' and finishDate<='"+date2+"' and proId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select count(*) from project as P,teacher as T where P.teaId=T.teaId and T.direction=? and state>'"+state+"' ";
			int count=0;
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			
			paras.add(String.valueOf(direction));
			if(project!=null)
			{
				if(project.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(project.getName())+"%");
				}
				if(project.getLevel()!=null)
				{
					sql1.add(" P.level=? ");
					paras.add(String.valueOf(project.getLevel()));
				}
				if(project.getFinishDate()!=null)
				{
					sql1.add(" P.finishDate>=? and P.finishDate<=? ");
					paras.add(String.valueOf(project.getBeginDate()));
					paras.add(String.valueOf(project.getFinishDate()));
				}
				if(project.getSource()!=null)
				{
					sql1.add(" P.source=? ");
					paras.add(String.valueOf(project.getSource()));
				}
				if(project.getFunds()!=null)
				{
					sql1.add(" P.funds=? ");
					paras.add(String.valueOf(project.getFunds()));
				}
				if(project.getArriveFunds()!=null)
				{
					sql1.add(" P.arriveFunds=? ");
					paras.add(String.valueOf(project.getArriveFunds()));
				}
				if(project.getLeader()!=null)
				{
					sql1.add(" P.leader like ? ");
					paras.add("%"+String.valueOf(project.getLeader())+"%");
				}
				if(project.getOthers()!=null)
				{
					sql1.add(" P.others like ? ");
					paras.add("%"+String.valueOf(project.getOthers())+"%");
				}
				if(project.getClass_()!=null)
				{
					sql1.add(" P.class=? ");
					paras.add(String.valueOf(project.getClass_()));
				}
				if(project.getNumber()!=null)
				{
					sql1.add(" P.number=? ");
					paras.add(String.valueOf(project.getNumber()));
				}
				if(project.getKind()!=null)
				{
					sql1.add(" P.kind=? ");
					paras.add(String.valueOf(project.getKind()));
				}
				if(project. getProgress()!=null)
				{
					sql1.add(" P.progress=? ");
					paras.add(String.valueOf(project. getProgress()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> lists=mysqlcurd.SelectData(sql, parameter);
			Map map=lists.get(0);
			count=Integer.parseInt(map.get(0).toString());
			return count;
		}
		/**
		 * @author yaojun
		 * @category 按照不同的条件来查询所有的项目,针对特定的教师工号
		 * @return projects List<Project> 返回查询到的论文列表
		 */
		public List<Project> getProjectByUser(Project project,Teacher teacher) {
//			String sql= "select * from project WHERE finishDate>='"+date1+"' and finishDate<='"+date2+"' and proId LIMIT " +
//					""+(pageNow-1)+" ,"+pageSize+"";
			String sql="select * from project as P,teacher as T where P.teaId=T.teaId and T.user=? ";
			int j=0;
			List<String> sql1=new ArrayList<String>();
			List<String> paras=new ArrayList<String>();
			List<Project> projects=new ArrayList<Project>();
			
			paras.add(teacher.getUser());
			if(project!=null)
			{
				if(project.getName()!=null)
				{
					sql1.add(" P.name like ? ");
					paras.add("%"+String.valueOf(project.getName())+"%");
				}
				if(project.getLevel()!=null)
				{
					sql1.add(" P.level=? ");
					paras.add(String.valueOf(project.getLevel()));
				}
				if(project.getFinishDate()!=null)
				{
					sql1.add(" P.finishDate>=? and P.finishDate<=? ");
					paras.add(String.valueOf(project.getBeginDate()));
					paras.add(String.valueOf(project.getFinishDate()));
				}
				if(project.getSource()!=null)
				{
					sql1.add(" P.source=? ");
					paras.add(String.valueOf(project.getSource()));
				}
				if(project.getFunds()!=null)
				{
					sql1.add(" P.funds=? ");
					paras.add(String.valueOf(project.getFunds()));
				}
				if(project.getArriveFunds()!=null)
				{
					sql1.add(" P.arriveFunds=? ");
					paras.add(String.valueOf(project.getArriveFunds()));
				}
				if(project.getLeader()!=null)
				{
					sql1.add(" P.leader like ? ");
					paras.add("%"+String.valueOf(project.getLeader())+"%");
				}
				if(project.getOthers()!=null)
				{
					sql1.add(" P.others like ? ");
					paras.add("%"+String.valueOf(project.getOthers())+"%");
				}
				if(project.getClass_()!=null)
				{
					sql1.add(" P.class=? ");
					paras.add(String.valueOf(project.getClass_()));
				}
				if(project.getNumber()!=null)
				{
					sql1.add(" P.number=? ");
					paras.add(String.valueOf(project.getNumber()));
				}
				if(project.getKind()!=null)
				{
					sql1.add(" P.kind=? ");
					paras.add(String.valueOf(project.getKind()));
				}
				if(project. getProgress()!=null)
				{
					sql1.add(" P.progress=? ");
					paras.add(String.valueOf(project. getProgress()));
				}
			}
			for(int i=0;i<sql1.size();i++)
			{
				sql+="and"+sql1.get(i);
			}
			String[] parameter=new String[paras.size()];
			for(int i=0;i<paras.size();i++)
			{
				parameter[i]=paras.get(i);
			}
			MysqlCrud mysqlcurd=new MysqlCrud();
			List<Map> list = mysqlcurd.SelectData(sql, parameter);
			//System.out.println(list.size());
			while(j<list.size()){
				Map map=list.get(j);
				projects.add(new MapToBean().MapToProject(map));
				j++;
			}
			return projects;
		}
}
